package com.hw01.mapper;

import com.hw01.dto.PayrollComputationDto;
import org.apache.ibatis.annotations.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.github.yulichang.base.mapper.MPJJoinMapper;
import com.hw01.entity.Payrolls;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 工资单 Mapper 接口
 * </p>
 *
 * @author zhong
 * @since 2024-03-18
 */
@Mapper
public interface PayrollsMapper extends BaseMapper<Payrolls>, MPJJoinMapper<Payrolls> {

    /**
     * 获取计算薪资
     * @param month 月份
     * @param firstDay 月份首日
     * @return 结果
     */
    @Select("<script>" +
            "select \n" +
            "    e.id as user_id\n" +
            "    , e.basic_salary\n" +
            "    , ifnull(b.grade, 0)\n" +
//            注解 使用ifnull函数来处理b.grade字段
            "    , ifnull(b.confirmed_workloads, 0) * 100 * ifnull(b.grade, 0) as bonus\n" +
            "    , (ifnull(c.working_days, 0) - ifnull(c.clock_in_num, 0)) * 100 AS deduct_expenses\n" +
            "from employee e \n" +
            "left join payrolls a on a.year = #{year} and a.month = #{month} and a.user_id = e.id \n" +
            "left join ( \n" +
            "    select \n" +
            "        t.user_id \n" +
            "        , case <![CDATA[ when t.percentage_complete < 0.5 then 0.5 ]]>\n" +
            "            when <![CDATA[ t.percentage_complete >= 0.5 and t.percentage_complete < 0.7 then 0.8 ]]>\n" +
            "            when <![CDATA[ t.percentage_complete >= 0.7 and t.percentage_complete < 0.9 then 1.2 ]]>\n" +
            "            else 1.5 \n" +
            "        end as grade \n" +
            "        , t.confirmed_workloads \n" +
            "    from ( \n" +
            "        select \n" +
            "            t1.user_id \n" +
            "            , round(ifnull(t1.confirmed_workloads, 0) / ifnull(ta.workloads, 100), 2) as percentage_complete \n" +
            "            , ifnull(t1.confirmed_workloads, 0) as confirmed_workloads \n" +
            "        from (\n" +
            "           select \n" +
            "               ew.user_id\n" +
            "               , sum(committed_workloads) AS confirmed_workloads\n" +
            "           from employee_workloads ew\n" +
            "           where ew.year = #{year}\n" +
            "             and ew.month = #{month}\n" +
            "             and ew.leader_certain = 1\n" +
            "           group by ew.user_id\n" +
            "        ) t1 \n" +
            "        left join task_allocation ta \n" +
            "               on ta.user_id = t1.user_id\n" +
            "              and ta.del_state = 0 \n" +
            "              and ta.year = #{year} \n" +
            "              and ta.month = #{month} \n" +
            "    ) t \n" +
            ") b on b.user_id = e.id\n" +
            "LEFT JOIN (\n" +
            "    select \n" +
            "        e.id as user_id\n" +
            "        , sum(\n" +
            "            case when <![CDATA[ pcr.clock_in >= e.date_on_board and pcr.clock_in <= ifnull(e.term_date, '9999-12-31') then 1 ]]>\n" +
            "            else 0\n" +
            "            end \n" +
            "        ) as clock_in_num\n" +
            "        , datediff(\n" +
            "            least(\n" +
            "                current_date()\n" +
            "                , least(\n" +
            "                    last_day(#{firstDay})\n" +
            "                    , ifnull(e.term_date, '9999-12-31')\n" +
            "                )\n" +
            "            )\n" +
            "            , greatest(e.date_on_board, #{firstDay})\n" +
            "        ) + 1 \n" +
            "        as working_days\n" +
            "    from employee e\n" +
            "    left join punching_card_record pcr \n" +
            "           on <![CDATA[ pcr.clock_in >= #{firstDay} ]]> \n" +
            "           and <![CDATA[ pcr.clock_in < cast(date_add(#{firstDay}, interval 1 month) as date) ]]>\n" +
            "           and pcr.user_id = e.id \n" +
            "    where e.del_state = 0\n" +
            "    and e.login_name != 'admin'\n" +
            "    group by e.id, e.term_date, e.date_on_board\n" +
            ") c ON c.user_id = e.id \n" +
            "where e.login_name != 'admin'\n" +
            "and e.del_state = 0\n" +
            "</script>")
    List<PayrollComputationDto> selectPayrollComputation(String year, String month, String firstDay);

}
